﻿using CountryDepth.Library.BusinessLogic;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace CountryDepth.Library.DataAccess
{
    public static partial class Main
    {
        #region Public Methods

        public static bool SaveVacation(BLVacationRequest vacation)
        {
            try
            {
                if (vacation.VacationId == 0)
                    return CreateVacation(vacation);
                else
                    return UpdateVacation(vacation);
            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static bool ApproveVacation(int vacationId, int userId)
        {
            try
            {
                // Approve Vacation

                // UPDATE Vacation Master
                command.CommandText = "usp_ApproveVacationRequest";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@VacationMasterId", vacationId));
                command.Parameters.Add(new SqlParameter("@UserId", userId));

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                command.ExecuteNonQuery();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return true;
            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        public static bool DeleteVacation(int vacationId)
        {
            try
            {
                // Delete Vaction Details
                DeleteVacationDetails(vacationId);

                // Delete Vacation Master
                DeleteVacationMaster(vacationId);

                return true;
            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }

        }

        #endregion

        #region Priavte Methods

        private static bool CreateVacation(BLVacationRequest vacation)
        {
            try
            {
                // If no vacation details exist
                if (vacation.VacationDetails.Count == 0)
                    return false;

                // CREATE Vacation Master
                command.CommandText = "usp_InsertVacationMaster";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@UserId", vacation.User.Id));
                command.Parameters.Add(new SqlParameter("@DateAdded", DateTime.Now));
                command.Parameters.Add(new SqlParameter("@VacationStartDate", vacation.StartDate));
                command.Parameters.Add(new SqlParameter("@VacationEndDate", vacation.EndDate));
                command.Parameters.Add(new SqlParameter("@Comment", vacation.Comment));
                command.Parameters.Add(new SqlParameter("@VacationTypeId", vacation.VacationType.Id));

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                int vacationMasterId = (Int32)command.ExecuteScalar();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                foreach (VacationDetail detail in vacation.VacationDetails)
                {
                    // save each vacation detail
                    command.CommandText = "usp_InsertVacationDetail";
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Clear();

                    command.Parameters.Add(new SqlParameter("VacationMasterId", vacationMasterId));
                    command.Parameters.Add(new SqlParameter("@Date", detail.VacationDate));
                    command.Parameters.Add(new SqlParameter("@VacationTypeId", vacation.VacationType.Id));

                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    command.ExecuteNonQuery();

                    if (connection.State != ConnectionState.Closed)
                        connection.Close();
                }
                return true;
            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        private static bool UpdateVacation(BLVacationRequest vacation)
        {
            try
            {
                if (vacation.Approved)
                    return false;

                if (vacation.VacationDetails.Count > 0)
                {
                    // UPDATE Vacation Master
                    command.CommandText = "usp_UpdateVacationMaster";
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Clear();

                    command.Parameters.Add(new SqlParameter("@VacationMasterId", vacation.VacationId));
                    command.Parameters.Add(new SqlParameter("@DateAdded", DateTime.Now));
                    command.Parameters.Add(new SqlParameter("@VacationStartDate", vacation.StartDate));
                    command.Parameters.Add(new SqlParameter("@VacationEndDate", vacation.EndDate));
                    command.Parameters.Add(new SqlParameter("@Comment", vacation.Comment));
                    command.Parameters.Add(new SqlParameter("@VacationTypeId", vacation.VacationType.Id));

                    if (connection.State != ConnectionState.Open)
                        connection.Open();

                    int vacationMasterId = (Int32)command.ExecuteScalar();

                    if (connection.State != ConnectionState.Closed)
                        connection.Close();


                    // DELETE Vacation Details
                    DeleteVacationDetails(vacation.VacationId);


                    // INSERT new vacation details
                    foreach (VacationDetail detail in vacation.VacationDetails)
                    {
                        // save each vacation detail
                        command.CommandText = "usp_InsertVacationDetail";
                        command.CommandType = CommandType.StoredProcedure;
                        command.Parameters.Clear();

                        command.Parameters.Add(new SqlParameter("VacationMasterId", vacation.VacationId));
                        command.Parameters.Add(new SqlParameter("@Date", detail.VacationDate));
                        command.Parameters.Add(new SqlParameter("@VacationTypeId", vacation.VacationType.Id));

                        if (connection.State != ConnectionState.Open)
                            connection.Open();

                        command.ExecuteNonQuery();

                        if (connection.State != ConnectionState.Closed)
                            connection.Close();
                    }
                }

                return true;
            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }
        }

        private static bool DeleteVacationDetails(int vacationId)
        {
            try
            {

                command.CommandText = "usp_DeleteVacationDetails";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@VacationMasterId", vacationId));

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                command.ExecuteNonQuery();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return true;
            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }

        }

        private static bool DeleteVacationMaster(int vacationId)
        {
            try
            {
                command.CommandText = "usp_DeleteVacationMasterRecord";
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Clear();

                command.Parameters.Add(new SqlParameter("@VacationMasterId", vacationId));

                if (connection.State != ConnectionState.Open)
                    connection.Open();

                command.ExecuteNonQuery();

                if (connection.State != ConnectionState.Closed)
                    connection.Close();

                return true;
            }
            catch (SqlException sqlExc)
            {
                return false;
            }
            catch (Exception exc)
            {
                return false;
            }

        }

        #endregion
    }
}
